﻿
using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Collections.Generic;

namespace Saturn.AppSQLite
{
    public static class SqliteHelper
    {
        /// <summary>
        /// 数据文件地址
        /// </summary>
        public static string DataBaseFile = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\DB\\Saturn.db";

        /// <summary>
        /// 数据连接字符串
        /// </summary>
        public static string SqlConnection = "Data Source=" + DataBaseFile + ";Pooling=true;FailIfMissing=false;";


        #region ******   ExecuteNonQuery   ******
        /// <summary>
        /// 执行数据库操作(新增、更新或删除)
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="cmd">SqlCommand对象</param>
        /// <returns>所受影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, SQLiteCommand cmd)
        {
            int result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);
                try
                {
                    result = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }

        /// <summary>
        /// 执行数据库操作(新增、更新或删除)
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>所受影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, string commandText, CommandType commandType)
        {
            int result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
                try
                {
                    result = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }

        /// <summary>
        /// 执行数据库操作(新增、更新或删除)
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <param name="cmdParms">SQL参数对象</param>
        /// <returns>所受影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)
        {
            int result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");

            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
                try
                {
                    result = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }



        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStringList">多条SQL语句</param>		
        public static int ExecuteSqlTran(string connectionString, List<String> sqlStringList)
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                var cmd = new SQLiteCommand
                {
                    Connection = conn
                };
                SQLiteTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    foreach (string strsql in sqlStringList)
                    {
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();


                    return count;
                }
                catch (Exception ex)
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }




        #endregion

        #region ******   ExecuteScalar   ******

        /// <summary>
        /// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="cmd">SqlCommand对象</param>
        /// <returns>查询所得的第1行第1列数据</returns>
        public static int ExecScalar(string connectionString, SQLiteCommand cmd)
        {
            int result = 0;
            object obj = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);
                try
                {
                    obj = cmd.ExecuteScalar();
                    trans.Commit();

                    if (obj != null)
                    {
                        result = (int)obj;
                    }

                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }


        /// <summary>
        /// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="cmd">SqlCommand对象</param>
        /// <returns>查询所得的第1行第1列数据</returns>
        public static object ExecuteScalar(string connectionString, SQLiteCommand cmd)
        {
            object result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);
                try
                {
                    result = cmd.ExecuteScalar();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }

        /// <summary>
        /// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>查询所得的第1行第1列数据</returns>
        public static object ExecuteScalar(string connectionString, string commandText, CommandType commandType)
        {
            object result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
                try
                {
                    result = cmd.ExecuteScalar();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }

        /// <summary>
        /// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <param name="cmdParms">SQL参数对象</param>
        /// <returns>查询所得的第1行第1列数据</returns>
        public static object ExecuteScalar(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)
        {
            object result = 0;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");

            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
                try
                {
                    result = cmd.ExecuteScalar();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }
        #endregion

        #region ******   ExecuteReader   ******
        /// <summary>
        /// 执行数据库查询，返回SqlDataReader对象
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="cmd">SqlCommand对象</param>
        /// <returns>SqlDataReader对象</returns>
        public static DbDataReader ExecuteReader(string connectionString, SQLiteCommand cmd)
        {
            DbDataReader reader = null;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");

            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, cmd.CommandType, cmd.CommandText);
            try
            {
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return reader;
        }

        /// <summary>
        /// 执行数据库查询，返回SqlDataReader对象
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>SqlDataReader对象</returns>
        public static DbDataReader ExecuteReader(string connectionString, string commandText, CommandType commandType)
        {
            DbDataReader reader = null;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");

            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, commandType, commandText);
            try
            {
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return reader;
        }

        /// <summary>
        /// 执行数据库查询，返回SqlDataReader对象
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <param name="cmdParms">SQL参数对象</param>
        /// <returns>SqlDataReader对象</returns>
        public static DbDataReader ExecuteReader(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)
        {
            DbDataReader reader = null;
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");

            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, commandType, commandText, cmdParms);
            try
            {
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return reader;
        }
        #endregion

        #region ******   ExecuteDataSet   ******
        /// <summary>
        /// 执行数据库查询，返回DataSet对象
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="cmd">SqlCommand对象</param>
        /// <returns>DataSet对象</returns>
        public static DataSet ExecuteDataSet(string connectionString, SQLiteCommand cmd)
        {
            DataSet ds = new DataSet();
            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, cmd.CommandType, cmd.CommandText);
            try
            {
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd.Connection != null)
                {
                    if (cmd.Connection.State == ConnectionState.Open)
                    {
                        cmd.Connection.Close();
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 执行数据库查询，返回DataSet对象
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>DataSet对象</returns>
        public static DataSet ExecuteDataSet(string connectionString, string commandText, CommandType commandType)
        {
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");
            DataSet ds = new DataSet();
            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, commandType, commandText);
            try
            {
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con != null)
                {
                    if (con.State == ConnectionState.Open)
                    {
                        con.Close();
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 执行数据库查询，返回DataSet对象
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <param name="cmdParms">SQL参数对象</param>
        /// <returns>DataSet对象</returns>
        public static DataSet ExecuteDataSet(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)
        {
            if (connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");
            DataSet ds = new DataSet();
            SQLiteConnection con = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, commandType, commandText, cmdParms);
            try
            {
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con != null)
                {
                    if (con.State == ConnectionState.Open)
                    {
                        con.Close();
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string sqlString)
        {
            using (var connection = new SQLiteConnection(SqlConnection))
            {
                var ds = new DataSet();
                try
                {
                    connection.Open();
                    var command = new SQLiteDataAdapter(sqlString, connection);
                    command.Fill(ds, "ds");
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }

                return ds;
            }
        }


        #endregion

        #region ******    SaveDataTable    ******
        /// <summary>
        /// 保存DataTable 
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <returns>影响的行数</returns>
        public static int SaveDataTable(DataTable dt, string tableName)
        {
            SQLiteConnection _con;
            SQLiteCommand command;
            _con = new SQLiteConnection(SqlConnection);
            command = _con.CreateCommand();
            SQLiteTransaction _tran = null;
            try
            {
                command.CommandText = "SELECT * FROM " + tableName;
                SQLiteDataAdapter oda = new SQLiteDataAdapter(command);
                SQLiteCommandBuilder ocb = new SQLiteCommandBuilder(oda);
                oda.InsertCommand = ocb.GetInsertCommand();
                oda.InsertCommand = ocb.GetUpdateCommand();
                oda.DeleteCommand = ocb.GetDeleteCommand();

                _con.Open();
                _tran = _con.BeginTransaction();
                command.Transaction = _tran;
                int result = oda.Update(dt);
                _tran.Commit();
                return result;
            }
            catch (Exception ex)
            {
                _tran.Rollback();
                throw new Exception("保存DataTable出错：" + ex.Message);
            }
            finally
            {
                _con.Close();
            }

        }
        #endregion

        #region ******    通用分页查询方法    ******
        /// <summary>
        /// 通用分页查询方法
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <param name="tableName">表名</param>
        /// <param name="strColumns">查询字段名</param>
        /// <param name="strWhere">where条件</param>
        /// <param name="strOrder">排序条件</param>
        /// <param name="pageSize">每页数据数量</param>
        /// <param name="currentIndex">当前页数</param>
        /// <param name="recordOut">数据总量</param>
        /// <returns>DataTable数据表</returns>
        public static DataTable SelectPaging(string connString, string tableName, string strColumns, string strWhere, string strOrder, int pageSize, int currentIndex, out int recordOut)
        {
            var dt = new DataTable();
            string strRecord = "select count(*) from " + tableName;
            if (!string.IsNullOrEmpty(strWhere))
            {
                strRecord += " where " + strWhere;
            }
            recordOut = Convert.ToInt32(ExecuteScalar(connString, strRecord, CommandType.Text));

            string pagingTemplate = "select {0} from {1} ";
            if (!string.IsNullOrEmpty(strWhere))
            {
                pagingTemplate += " where {2} ";
            }
            if (!string.IsNullOrEmpty(strOrder))
            {
                pagingTemplate += " order by {3} ";
            }
            pagingTemplate += " limit {4} offset {5} ";

            int offsetCount = (currentIndex - 1) * pageSize;
            string commandText = string.Format(pagingTemplate, strColumns, tableName, strWhere, strOrder, pageSize.ToString(), offsetCount.ToString());
            using (DbDataReader reader = ExecuteReader(connString, commandText, CommandType.Text))
            {
                if (reader != null)
                {
                    dt.Load(reader);
                }
            }
            return dt;
        }
        #endregion

        #region 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化
        /// <summary>
        /// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <param name="conn">Connection对象</param>
        /// <param name="trans">Transcation对象</param>
        /// <param name="useTrans">是否使用事务</param>
        /// <param name="cmdType">SQL字符串执行类型</param>
        /// <param name="cmdText">SQL Text</param>
        /// <param name="cmdParms">SQLiteParameters to use in the command</param>
        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, ref SQLiteTransaction trans, bool useTrans, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (useTrans)
            {
                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = trans;
            }


            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SQLiteParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion


        // 一、批量插入带测试
        public static int FastInsertMany(string connectionString)
        {

            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                //var cmd = new SQLiteCommand
                //{
                //    Connection = conn
                //};
                using (DbTransaction dbTrans = conn.BeginTransaction())
                {
                    using (DbCommand cmd = conn.CreateCommand())
                    {
                        try
                        {
                            cmd.CommandText = "INSERT INTO BillMain(BillId,BillCode) VALUES(?,?)";
                            DbParameter BillId = cmd.CreateParameter();
                            cmd.Parameters.Add(BillId);

                            DbParameter BillCode = cmd.CreateParameter();
                            cmd.Parameters.Add(BillCode);

                            for (int n = 0; n < 3; n++)
                            {
                                BillId.Value = n + 200000;
                                BillCode.Value = n + 200000;
                                cmd.ExecuteNonQuery();
                            }
                            dbTrans.Commit();
                        }
                        catch (Exception e)
                        {

                            dbTrans.Rollback();
                        }
                    }



                }
                return 0;
            }



        }

        //二、批量插入
        public static int FastInsert(string connectionString, EnumerableRowCollection<DataRow> dtBill, out string BillIds)
        {

            BillIds = "";
            try
            {
                int i = 0;
                using (var conn = new SQLiteConnection(connectionString))
                {
                    conn.Open();
                    //var cmd = new SQLiteCommand
                    //{
                    //    Connection = conn
                    //};
                    System.Text.StringBuilder builder = new System.Text.StringBuilder();
                    using (DbTransaction dbTrans = conn.BeginTransaction())
                    {
                        using (DbCommand cmd = conn.CreateCommand())
                        {
                            try
                            {
                                cmd.Transaction = dbTrans;
                                cmd.CommandText = "INSERT INTO BillMain(BillId,BillCode,BillDate,status,SendOrgCode,ReciveOrgCode,BillType,OutPlanCount) VALUES(?,?,?,?,?,?,?,?)";
                                #region 传参赋值
                                DbParameter BillId = cmd.CreateParameter();
                                cmd.Parameters.Add(BillId);

                                DbParameter BillCode = cmd.CreateParameter();
                                cmd.Parameters.Add(BillCode);

                                DbParameter BillDate = cmd.CreateParameter();
                                cmd.Parameters.Add(BillDate);

                                DbParameter status = cmd.CreateParameter();
                                cmd.Parameters.Add(status);

                                DbParameter SendOrgCode = cmd.CreateParameter();
                                cmd.Parameters.Add(SendOrgCode);

                                DbParameter ReciveOrgCode = cmd.CreateParameter();
                                cmd.Parameters.Add(ReciveOrgCode);

                                DbParameter BillType = cmd.CreateParameter();
                                cmd.Parameters.Add(BillType);

                                DbParameter OutPlanCount = cmd.CreateParameter();
                                cmd.Parameters.Add(OutPlanCount);
                                #endregion

                                foreach (var row in dtBill)
                                {

                                    BillIds += row["id"].ToString() + ","; // 1,2,3,4,
                                    //var ds = new DataSet();
                                    //var command = new SQLiteDataAdapter("select count(1) from BillMain where BillId = '" + row["id"].ToString() + "'", conn);
                                    //command.Fill(ds, "ds");
                                    //////可以考虑用   INSERT OR IGNORE INTO yourtable (field1,field2) VALUES(1,2)  这个语句， 如果存在就忽略，不存在就插入
                                    //if (ds.Tables[0].Rows.Count == 0)
                                    //{
                                    BillId.Value = row["id"].ToString();
                                    BillCode.Value = row["BillCode"].ToString();
                                    BillDate.Value = DateTime.Parse(row["BillDate"].ToString()).ToString("s");
                                    status.Value = 0;//(int)EnumClass.BillStatus.Downloaded
                                    SendOrgCode.Value = row["SendOrgCode"].ToString();
                                    ReciveOrgCode.Value = row["ReciveOrgCode"].ToString();
                                    BillType.Value = 0;//(int)EnumClass.BillType.BillTypeSAP
                                    OutPlanCount.Value = row["OutPlanCount"].ToString();
                                    try
                                    {
                                        if (cmd.ExecuteNonQuery() == 1)
                                        {
                                            i++;
                                        }

                                    }
                                    catch (Exception ex)
                                    {
                                        return 0;
                                    }

                                    //}
                                }
                                dbTrans.Commit();
                                BillIds = "'" + BillIds.TrimEnd(',').Replace(",", "','") + "'"; // '1','2','3','4'
                            }
                            catch (Exception e)
                            {

                                dbTrans.Rollback();
                            }
                        }
                    }
                }
                return i;

            }
            catch (Exception ex)
            {
                return 0;
            }
        }


        public static int FastInsert(string connectionString, DataTable dtBill, out string BillIds)
        {

            BillIds = "";
            try
            {
                int i = 0;
                using (var conn = new SQLiteConnection(connectionString))
                {
                    conn.Open();
                    //var cmd = new SQLiteCommand
                    //{
                    //    Connection = conn
                    //};
                    System.Text.StringBuilder builder = new System.Text.StringBuilder();
                    using (DbTransaction dbTrans = conn.BeginTransaction())
                    {
                        using (DbCommand cmd = conn.CreateCommand())
                        {
                            try
                            {
                                cmd.Transaction = dbTrans;
                                cmd.CommandText = "INSERT INTO BillMain(BillId,BillCode,BillDate,status,SendOrgCode,ReciveOrgCode,BillType,OutPlanCount) VALUES(?,?,?,?,?,?,?,?)";
                                #region 传参赋值
                                DbParameter BillId = cmd.CreateParameter();
                                cmd.Parameters.Add(BillId);

                                DbParameter BillCode = cmd.CreateParameter();
                                cmd.Parameters.Add(BillCode);

                                DbParameter BillDate = cmd.CreateParameter();
                                cmd.Parameters.Add(BillDate);

                                DbParameter status = cmd.CreateParameter();
                                cmd.Parameters.Add(status);

                                DbParameter SendOrgCode = cmd.CreateParameter();
                                cmd.Parameters.Add(SendOrgCode);

                                DbParameter ReciveOrgCode = cmd.CreateParameter();
                                cmd.Parameters.Add(ReciveOrgCode);

                                DbParameter BillType = cmd.CreateParameter();
                                cmd.Parameters.Add(BillType);

                                DbParameter OutPlanCount = cmd.CreateParameter();
                                cmd.Parameters.Add(OutPlanCount);
                                #endregion
                                //var ds = new DataSet();
                                int dtStart = Environment.TickCount;
                                foreach (DataRow row in dtBill.Rows)
                                {
                                    var ds = new DataSet();
                                    var command = new SQLiteDataAdapter("select count(1) from BillMain where BillId = '" + row["id"].ToString() + "'", conn);
                                    command.Fill(ds, "ds");
                                    ////可以考虑用   INSERT OR IGNORE INTO yourtable (field1,field2) VALUES(1,2)  这个语句， 如果存在就忽略，不存在就插入
                                    if (ds.Tables[0].Rows[0][0].ToString() == "0")
                                    {
                                        BillIds += row["id"].ToString() + ","; // 1,2,3,4,
                                        BillId.Value = row["id"].ToString();
                                        BillCode.Value = row["BillCode"].ToString();
                                        BillDate.Value = DateTime.Parse(row["BillDate"].ToString()).ToString("s");
                                        status.Value = 0;//(int)EnumClass.BillStatus.Downloaded
                                        SendOrgCode.Value = row["SendOrgCode"].ToString();
                                        ReciveOrgCode.Value = row["ReciveOrgCode"].ToString();
                                        BillType.Value = 0;//(int)EnumClass.BillType.BillTypeSAP
                                        OutPlanCount.Value = row["OutPlanCount"].ToString();
                                        try
                                        {
                                            if (cmd.ExecuteNonQuery() == 1)
                                            {
                                                i++;
                                            }

                                        }
                                        catch (Exception ex)
                                        {
                                            return 0;
                                        }

                                    }
                                }
                                dbTrans.Commit();
                                int dtEnd = Environment.TickCount;
                                dtEnd -= dtStart;
                                BillIds = "'" + BillIds.TrimEnd(',').Replace(",", "','") + "'"; // '1','2','3','4'
                            }
                            catch (Exception e)
                            {

                                dbTrans.Rollback();
                            }
                        }
                    }
                }
                return i;

            }
            catch (Exception ex)
            {
                return 0;
            }
        }
    }
}
